# -*- coding: utf-8 -*-
'''
@author: Script Team
@Created on: 2011-7-13
@version: 2011.7.13
@summary: Definition of DatabaseHandler class
'''
import os, sys, string
import MySQLdb

class DatabaseHandler(object):
    '''
    Database related operation.
    '''
    def __init__(self, host, user, password, database):
        '''
        Constructor.
        Keyword arguments:
        host -- database host
        user -- database username
        password -- database password
        database -- database name
        '''
        print "databasehandler -- __init__ "
        print "databasehandler -- __init__ :host = " + str(host)
        print "databasehandler -- __init__ :host = " + str(user)
        print "databasehandler -- __init__ :host = " + str(password)
        print "databasehandler -- __init__ :host = " + str(database)
        try:
            self.con = MySQLdb.connect(host, user, password, database)
            self.cursor = self.con.cursor()
        except Exception, e:
            print "databasehandler -- __init__ Exception:" + str(e)
        
    def insert(self, table, result_dict):
        '''
        Insert a new data item database table.
        Keyword arguments:
        table -- table name
        columnList -- field list to be inserted
        valueList -- value list to be inserted
        '''
        print "databasehandler -- insert "
        try:
            field_list = []
            value_list = []
            for (field, value) in result_dict.items():
                field_list.append(field)
                value_list.append(value)
                
            fields = ",".join(field_list)
            values = ""
            for i in range(len(value_list)):
                if value_list[i] != 'NULL':
                    values += "'" + str(value_list[i]) + "'"
                else:
                    values += value_list[i]
                if i < len(value_list) - 1:
                    values += ", "
            values = values.replace('\\', '/')
            
            if fields != '':
                sql = "INSERT INTO %s" % (table) + " (%s" % (fields) \
                    + ") VALUES (%s" % (values) + ")"
            else:
                sql = "INSERT INTO %s" % (table) \
                    + " VALUES (%s" % (values) + ")"
            print "databasehandler -- insert: sql = " + sql
            self.cursor.execute(sql)
            self.con.commit()
            
            return True
        except Exception, e:
            print 'DB insert - Exception: ' + str(e)
            return False
        
    def get(self, table):
        print "databasehandler -- get: sql = " + "select * from %s" % (table)
        self.cursor.execute("select * from %s" % (table))
        alldata = self.cursor.fetchall()
        return alldata
    
    def set(self, table, setField, setValue, whereField, whereValue):
        '''
        Update data to database table.
        Keyword arguments:
        table -- table name
        setField -- field to be updated
        setValue -- value to be updated
        whereField -- where conditon field
        whereValue -- where conditon value
        '''
        try:
            sql = "UPDATE " + table + " SET " + setField + " = " + setValue + \
                  " WHERE " + whereField + " = " + whereValue
            self.cursor.execute(sql)
            self.con.commit()
        
            return True
        except Exception, e:
            print 'DB set - Exception: ' + str(e)
            return False
        
    def close(self):
        self.cursor.close()
        self.con.close()